﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using PMS.ENT;
using System.Data.SqlClient;
using PMS.Utilities;


namespace PMS.DAL
{
    public class ScheduleItemDAL
    {
        #region Common
        private static ScheduleItemENT CreateScheduleItemFromReader(IDataReader reader)
        {
            ScheduleItemENT item = new ScheduleItemENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.ScheduleItemID.ToString()))) item.ScheduleItemID = Utility.IntParse(reader[ScheduleItemColumns.ScheduleItemID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.ScheduleID.ToString()))) item.ScheduleID = Utility.IntParse(reader[ScheduleItemColumns.ScheduleID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.FromDate.ToString()))) item.FromDate = Utility.DateTimeParse(reader[ScheduleItemColumns.FromDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.ToDate.ToString()))) item.ToDate = Utility.DateTimeParse(reader[ScheduleItemColumns.ToDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.Description.ToString()))) item.Description = Utility.StringParse(reader[ScheduleItemColumns.Description.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.Percent.ToString()))) item.Percent = Utility.DecimalParse(reader[ScheduleItemColumns.Percent.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.Work.ToString()))) item.Work = Utility.StringParse(reader[ScheduleItemColumns.Work.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.Status.ToString()))) item.Status = Utility.StringParse(reader[ScheduleItemColumns.Status.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[ScheduleItemColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[ScheduleItemColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[ScheduleItemColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[ScheduleItemColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[ScheduleItemColumns.Isdeleted.ToString()]);

                DataTable dt = reader.GetSchemaTable();
                if (Utility.FieldExist(dt, ScheduleItemColumns.ScheduleStatementID.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.ScheduleStatementID.ToString()))) item.ScheduleStatementID = Utility.IntParse(reader[ScheduleItemColumns.ScheduleStatementID.ToString()]);

                }
                if (Utility.FieldExist(dt, ScheduleItemColumns.Mark.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.Mark.ToString()))) item.Mark = Utility.DecimalParse(reader[ScheduleItemColumns.Mark.ToString()]);

                }
                if (Utility.FieldExist(dt, ScheduleItemColumns.LineNet.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ScheduleItemColumns.LineNet.ToString()))) item.LineNet = Utility.DecimalParse(reader[ScheduleItemColumns.LineNet.ToString()]);

                }
            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static ScheduleItemENT GetScheduleItemByID(int scheduleItemID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemGetByID.ToString());
                database.AddInParameter(dbCommand, "@ScheduleItemID", DbType.Int32, scheduleItemID);

                ScheduleItemENT scheduleItem = new ScheduleItemENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        scheduleItem = CreateScheduleItemFromReader(reader);
                    }
                    reader.Close();
                }

                return scheduleItem;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleItemByID");
            }
        }


        public static ScheduleItemCollection GetScheduleItemList(string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemGetList.ToString());
                ScheduleItemCollection listScheduleItem = new ScheduleItemCollection();

                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listScheduleItem.Add(CreateScheduleItemFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listScheduleItem;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleItemList");
            }
        }

        public static ScheduleItemCollection GetScheduleItemListByScheduleID(int scheduleID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemGetListByScheduleID.ToString());
                ScheduleItemCollection listScheduleItem = new ScheduleItemCollection();

                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleID);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listScheduleItem.Add(CreateScheduleItemFromReader(reader));
                    }
                    reader.Close();
                }


                return listScheduleItem;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleItemListByScheduleID");
            }
        }

        public static ScheduleItemCollection GetScheduleItemListByScheduleGroupID(int scheduleGroupID, out int groupID, out int projectOwnerID, out int task, out decimal percent, out decimal total)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemGetListByScheduleGroupID.ToString());
                ScheduleItemCollection listScheduleItem = new ScheduleItemCollection();

                task = 0;
                percent = 0;
                total = 0;

                database.AddInParameter(dbCommand, "@ScheduleGroupID", DbType.Int32, scheduleGroupID);
                database.AddOutParameter(dbCommand, "@GroupID", DbType.Int32, 0);
                database.AddOutParameter(dbCommand, "@ProjectOwnerID", DbType.Int32, 0);
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        ScheduleItemENT scheduleItem = CreateScheduleItemFromReader(reader);
                        listScheduleItem.Add(scheduleItem);
                        if (scheduleItem.ToDate < DateTime.Today)
                        {
                            task += 1;
                            percent += scheduleItem.Percent;
                            total += scheduleItem.LineNet;
                        }
                    }
                    reader.Close();
                }

                groupID = Utility.IntParse(database.GetParameterValue(dbCommand, "@GroupID"));
                projectOwnerID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ProjectOwnerID"));
                return listScheduleItem;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleItemListByScheduleID");
            }
        }

        public static ScheduleItemCollection GetScheduleItemListForReport(int groupID, int projectID, int semesterID, out int task, out decimal percent, out decimal total)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProgressRpt.ToString());
                ScheduleItemCollection listScheduleItem = new ScheduleItemCollection();

                task = 0;
                percent = 0;
                total = 0;

                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        ScheduleItemENT scheduleItem = CreateScheduleItemFromReader(reader);
                        listScheduleItem.Add(scheduleItem);

                        task += 1;
                        percent += scheduleItem.Percent;
                        total += scheduleItem.LineNet;

                    }
                    reader.Close();
                }

                return listScheduleItem;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleItemListForReport");
            }
        }

        public static ScheduleItemCollection GetScheduleItemListForCMB(int userID, int projectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemGetListForCMB.ToString());
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);

                ScheduleItemCollection listScheduleItem = new ScheduleItemCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listScheduleItem.Add(CreateScheduleItemFromReader(reader));
                    }
                    reader.Close();
                }

                return listScheduleItem;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleItemListForCMB");
            }
        }

        public static ScheduleItemCollection GetScheduleItemListByStatus(string scheduleItemID, string status)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemGetListByStatus.ToString());
                database.AddInParameter(dbCommand, "@ScheduleItemID", DbType.String, scheduleItemID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);

                ScheduleItemCollection listScheduleItem = new ScheduleItemCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listScheduleItem.Add(CreateScheduleItemFromReader(reader));
                    }
                    reader.Close();
                }

                return listScheduleItem;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleItemListForCMB");
            }
        }
        #endregion

        #region Insert, Update, Delete

        public static int InsertScheduleItem(ScheduleItemENT scheduleItem)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemInsert.ToString());
                int scheduleItemID = 0;
                database.AddInParameter(dbCommand, "@ScheduleItemID", DbType.Int32, scheduleItem.ScheduleItemID);
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleItem.ScheduleID);
                database.AddInParameter(dbCommand, "@FromDate", DbType.DateTime, scheduleItem.FromDate);
                database.AddInParameter(dbCommand, "@ToDate", DbType.DateTime, scheduleItem.ToDate);
                database.AddInParameter(dbCommand, "@Description", DbType.String, scheduleItem.Description);
                database.AddInParameter(dbCommand, "@Work", DbType.String, scheduleItem.Work);
                database.AddInParameter(dbCommand, "@Percent", DbType.Decimal, scheduleItem.Percent);
                database.AddInParameter(dbCommand, "@Status", DbType.String, scheduleItem.Status);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, scheduleItem.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, scheduleItem.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, scheduleItem.Isdeleted);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    scheduleItemID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ScheduleItemID"));
                    return scheduleItemID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertPriceList");
            }
        }

        public static int InsertScheduleItem(ScheduleItemENT scheduleItem, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemInsert.ToString());
                int scheduleItemID = 0;
                database.AddOutParameter(dbCommand, "@ScheduleItemID", DbType.Int32, scheduleItem.ScheduleItemID);
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleItem.ScheduleID);
                database.AddInParameter(dbCommand, "@FromDate", DbType.DateTime, scheduleItem.FromDate);
                database.AddInParameter(dbCommand, "@ToDate", DbType.DateTime, scheduleItem.ToDate);
                database.AddInParameter(dbCommand, "@Description", DbType.String, scheduleItem.Description);
                database.AddInParameter(dbCommand, "@Work", DbType.String, scheduleItem.Work);
                database.AddInParameter(dbCommand, "@Percent", DbType.Decimal, scheduleItem.Percent);
                database.AddInParameter(dbCommand, "@Status", DbType.String, scheduleItem.Status);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, scheduleItem.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, scheduleItem.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, scheduleItem.Isdeleted);

                database.ExecuteNonQuery(dbCommand, trans);

                scheduleItemID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ScheduleItemID"));
                return scheduleItemID;


            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertScheduleItem");
            }
        }

        public static int UpdateScheduleItem(ScheduleItemENT scheduleItem, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemUpdate.ToString());

                database.AddInParameter(dbCommand, "@ScheduleItemID", DbType.Int32, scheduleItem.ScheduleItemID);
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleItem.ScheduleID);
                database.AddInParameter(dbCommand, "@FromDate", DbType.DateTime, scheduleItem.FromDate);
                database.AddInParameter(dbCommand, "@ToDate", DbType.DateTime, scheduleItem.ToDate);
                database.AddInParameter(dbCommand, "@Description", DbType.String, scheduleItem.Description);
                database.AddInParameter(dbCommand, "@Work", DbType.String, scheduleItem.Work);
                database.AddInParameter(dbCommand, "@Percent", DbType.Decimal, scheduleItem.Percent);
                database.AddInParameter(dbCommand, "@Status", DbType.String, scheduleItem.Status);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, scheduleItem.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, scheduleItem.Isdeleted);

                if (database.ExecuteNonQuery(dbCommand, trans) > 0)
                {
                    int scheduleItemID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ScheduleItemID"));
                    return scheduleItemID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertPriceList");
            }
        }

        public static int DeleteScheduleItem(string scheduleItemID, int userID, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemDelete.ToString());
                database.AddInParameter(dbCommand, "@ScheduleItemID", DbType.String, scheduleItemID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand, trans) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteScheduleItem");
            }
        }

        public static int DeleteScheduleItemNotInID(string scheduleItemID, int scheduleID, int userID, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleItemDeleteNotInID.ToString());
                database.AddInParameter(dbCommand, "@ScheduleItemID", DbType.String, scheduleItemID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleID);
                database.ExecuteNonQuery(dbCommand, trans);
                return 1;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteScheduleItem");
            }
        }
        #endregion

        #region Validate
        public static bool CheckScheduleItemStatus(int[] scheduleItemID, string status)
        {
            try
            {
                if (scheduleItemID.Length == GetScheduleItemListByStatus(Utility.StringParse(scheduleItemID), status).Count)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_CheckScheduleItemStatus");
            }
        }
        #endregion
    }
}
